package yun.web.activerecord.dialect.pg;

import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.fastjson.JSONObject;
import org.postgresql.core.Field;
import org.postgresql.jdbc.PgResultSetMetaData;
import yun.web.activerecord.dialect.pg.type.PG;
import yun.web.activerecord.generator.ColumnMeta;
import yun.web.activerecord.generator.MetaBuilder;
import yun.web.activerecord.generator.TableMeta;
import yun.web.enjoy.kit.StrKit;

import javax.sql.DataSource;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class PGMetaBuilder extends MetaBuilder {
    DataSource ds;
    JSONObject schemas;

    public PGMetaBuilder(DataSource ds, JSONObject schemas) {
        super(ds);
        this.ds = ds;
        this.schemas = schemas;
    }

    /*
     @author 张天笑
     @time 2019/1/25 6:25

     查库获取table元信息 和 主键
    */
    @Override
    public List<TableMeta> build() {
        System.out.println("构建table信息 ...");
        try {
            conn = dataSource.getConnection();
            dbMeta = conn.getMetaData();

            ArrayList<PGTableMeta> origin_ret = new ArrayList<PGTableMeta>();
            _buildTableNames(origin_ret);

            ArrayList<TableMeta> result = new ArrayList<>();

            for (PGTableMeta it : origin_ret) {
                if (schemas.containsKey(it.schema_name)) {
                    // 获取主键
                    buildPrimaryKey(it);
                    // 获取字段信息
                    _buildColumnMetas(it);
                    result.add(it);
                }
            }

            return result;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }
        }
    }

    /*
     *  查库构建table信息
     * */
    public void _buildTableNames(ArrayList<PGTableMeta> ret) throws Exception {
        ResultSet rs = getTablesResultSet();
        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");

            if (excludedTables.contains(tableName)) {
                System.out.println("Skip table :" + tableName);
                continue;
            }
            if (isSkipTable(tableName)) {
                System.out.println("Skip table :$tableName");
                continue;
            }

            // 跳过table
            for (String excludedTable : excludedTables) {
                if (excludedTable.equals(tableName) || ReUtil.isMatch(excludedTable, tableName)) {
                    System.out.println("Skip table : " + tableName);
                    continue;
                }
            }

            PGTableMeta tableMeta = new PGTableMeta();

            // schema名称
            tableMeta.schema_name = rs.getString("table_schem");

            // 避开java关键字, public
            if ("public".equals(tableMeta.schema_name)) tableMeta.schema_name = "_public";

            // 表名
            tableMeta.name = tableName;
            // 表备注
            tableMeta.remarks = rs.getString("REMARKS");

            // 表对应的java类的名字
            tableMeta.modelName = buildModelName(tableName);

            //java类对应的baseModel的名字
            tableMeta.baseModelName = buildBaseModelName(tableMeta.modelName);

            ret.add(tableMeta);
        }
        rs.close();
    }

    public void _buildColumnMetas(PGTableMeta tableMeta) throws Exception {
        // 避开public关键字
        String schema_name = "_public".equals(tableMeta.schema_name) ? "public" : tableMeta.schema_name;
        String sql = this.dialect.forTableBuilderDoBuild(schema_name + "." + tableMeta.name);   // select * from $table where 1 = 2
        Statement stm = this.conn.createStatement();
        ResultSet rs = stm.executeQuery(sql);
        PgResultSetMetaData rsmd = (PgResultSetMetaData) rs.getMetaData();
        // fileds: [ org.postgresql.core.Field ]
        // columnCount 列数

        for (int i = 1; i <= rsmd.getColumnCount(); i++) {

            ColumnMeta cm = new ColumnMeta();

            // System.out.println(" pg driver 手动改为public");
            Method getFieldMethodObj = ReflectUtil.getMethod(PgResultSetMetaData.class, "getField", Integer.class);
            getFieldMethodObj.setAccessible(true);

            Field field = (Field) getFieldMethodObj.invoke(rsmd, i);

            // 字段名
            cm.name = field.getColumnLabel();

            //System.out.println(field.getOID() + "   " + cm.name);
            // 对应的get set 的方法名
            cm.attrName = this.buildAttrName(cm.name);

            // 从pg的api获取与java类型的映射
            cm.javaType = PG.OID_MAP.get(field.getOID());
            if (cm.javaType == null) cm.javaType = "org.postgresql.util.PGobject";
            //println("${type ?: 999999}  $typeStr  ${cm.attrName} ")
            build_column_remark(cm, schema_name, tableMeta.name);
            tableMeta.columnMetas.add(cm);
        }

        rs.close();
        stm.close();
    }

    public void build_column_remark(ColumnMeta cm
                                    //, dbname: String
            , String schema_name
            , String table_name) throws Exception {
        String sql = " select\n" +
                "        pgd.description as remark\n" +
                "        from information_schema.columns c\n" +
                "        inner join pg_catalog.pg_statio_all_tables st\n" +
                "        on c.table_schema = st.schemaname and c.table_name = st.relname\n" +
                "        left join pg_catalog.pg_description pgd\n" +
                "        on (pgd.objoid = st.relid and pgd.objsubid = c.ordinal_position)\n" +
                "        where 1 = 1\n" +
                "                --and c.table_catalog = $ dbname\n" +
                "        and c.table_schema = '" + schema_name + "'\n" +
                "        and c.table_name = '$table_name'\n" +
                "        and c.column_name = '${cm.name}'";

        Statement stm = this.conn.createStatement();
        ResultSet rs = stm.executeQuery(sql);
        if (rs.next()) {
            String string = rs.getString(1);
            cm.remarks = string;
        }
        rs.close();
        stm.close();
    }


    public void buildPrimaryKey(PGTableMeta tableMeta) throws Exception {
        String sql = "select null as table_cat,\n" +
                "       n.nspname  as table_schem,\n" +
                "       ct.relname as table_name,\n" +
                "       a.attname  as column_name,\n" +
                "       (i.keys).n as key_seq,\n" +
                "       ci.relname as pk_name\n" +
                "from pg_catalog.pg_class ct\n" +
                "       join pg_catalog.pg_attribute a on (ct.oid = a.attrelid)\n" +
                "       join pg_catalog.pg_namespace n on (ct.relnamespace = n.oid)\n" +
                "       join (select i.indexrelid, i.indrelid, i.indisprimary, information_schema._pg_expandarray(i.indkey) as keys\n" +
                "             from pg_catalog.pg_index i) i on (a.attnum = (i.keys).x and a.attrelid = i.indrelid)\n" +
                "       join pg_catalog.pg_class ci on (ci.oid = i.indexrelid)\n" +
                "where true\n" +
                "    and n.nspname||'.'||ct.relname = ?\n" +
                "  and i.indisprimary\n" +
                "order by table_name, pk_name, key_seq";
        PreparedStatement pst = this.conn.prepareStatement(sql);

        // 避开public关键字
        String schema_name = "_public".equals(tableMeta.schema_name) ? "public" : tableMeta.schema_name;

        pst.setString(1, schema_name + "." + tableMeta.name);
        ResultSet rs = pst.executeQuery();
        String primaryKey = "";
        int index = 0;

        while (rs.next()) {
            if (index++ > 0) {
                primaryKey += ",";
            }
            primaryKey += rs.getString("COLUMN_NAME");
        }
        if (StrKit.isBlank(primaryKey)) {
            System.err.println("primaryKey of table \"" + tableMeta.name + "\" required by active record pattern");
        }
        tableMeta.primaryKey = primaryKey;

        pst.close();
        rs.close();
    }
}
